# -*- coding: utf-8 -*-
import json
import logging
from datetime import datetime
from decimal import Decimal

from async import async_job
from common import orm, slave
from common.account.model.account import Account, BALANCE_TYPE
from common.pay.model import PAY_STATUS, Pay, PAY_TYPE
from common.preset.db.pay import get_pay_bonus_rate
from common.transaction import calc_withdraw_amount
from common.transaction.model import (TRANSACTION_STATUS,
                                      TRANSACTION_TYPE, Withdraw,
                                      WITHDRAW_STATUS, WITHDRAW_TYPE, TITLE_MAPPER, TRANSACTION_SHARDING_CONFIG,
                                      TRANSACTION_SHARDING_COUNT, CHECK_STATUS, Transaction, RISK_STATUS)
from common.utils import exceptions as err
from common.utils import tracker
from common.utils.db import get_count
from common.utils.db import list_object
from common.utils.decorator import sql_wrapper, slave_wrapper
from common.utils.exceptions import DbError
from common.utils.helper import (
    get_orderby, parse_query_dct, paginate, generate_filter)
from common.utils.tz import local_now, utc_to_local_str
from common.utils.api import check_params
from common.utils.currency import convert_yuan_to_hao, convert_hao_to_yuan
from common.cache.bull_cache import check_banker_lock, get_lock_amount, BULL_LOCK_RATIO
from sqlalchemy import func
import copy
from common.prize.db import calc_first_award

_LOGGER = logging.getLogger(__name__)

_FRESH_AWARD_PRICE = 1.0

MAX_BONUS_RATE = 0.1
AVAILABLE_BONUS_PAY_TYPE = [
    PAY_TYPE.UNIONAGENCY,
    PAY_TYPE.UNIONAGENCY_V2_ALI,
    PAY_TYPE.UNIONAGENCY_V2_BANKCARD,
    PAY_TYPE.UNIONAGENCY_V2_WX,
]
MIN_BONUS_RATE_PAY_AMOUNT = 5


@sql_wrapper
def list_transaction(user_id, query_dct):
    return list_object(query_dct, get_sharding_transaction_table(user_id))


@sql_wrapper
def list_transaction(user_id, query_dct):
    table = get_sharding_transaction_table(user_id)
    query, total_count = list_object(query_dct, table, disable_paginate=True)
    total_income = query.with_entities(func.sum(table.price)).filter(table.price > 0).scalar()
    total_outcome = query.with_entities(func.sum(table.price)).filter(table.price < 0).scalar()
    query = paginate(query, query_dct)
    return query.all(), total_count, total_income, total_outcome


@slave_wrapper
def get_transactions(user_id, limit=0, offset=0, transaction_type=None, start_date=None):
    transaction_table = get_sharding_transaction_table(user_id)
    query = slave.session.query(transaction_table).filter(transaction_table.user_id == user_id).with_hint(transaction_table, 'USE INDEX (user_id)')

    if transaction_type is not None:
        if transaction_type == TRANSACTION_TYPE.IN:
            query = query.filter(transaction_table.type.in_([TRANSACTION_TYPE.IN]))
        elif transaction_type == TRANSACTION_TYPE.WITHDRAW:
            query = query.filter(
                transaction_table.type.in_([TRANSACTION_TYPE.WITHDRAW, TRANSACTION_TYPE.WITHDRAW_FAIL_REFUND]))
        elif transaction_type == TRANSACTION_TYPE.ACTIVITY:
            query = query.filter(
                transaction_table.type.in_([TRANSACTION_TYPE.SYSTEM_RECHARGE,
                                            TRANSACTION_TYPE.PAY_BONUS, TRANSACTION_TYPE.RECHARGE_AWARD]))
        else:
            query = query.filter(transaction_table.type.op('&')(transaction_type) > 0)
    if start_date is not None:
        query = query.filter(transaction_table.created_at >= start_date)
    query = query.order_by(transaction_table.updated_at.desc())
    if limit:
        query = query.limit(limit)
    if offset:
        query = query.offset(offset)
    return query.all()


@sql_wrapper
def add_pay_wait_transaction(user_id, pay_id, pay_amount, extend):
    """
    充值等待确认
    """
    # update pay record status
    res = Pay.query.filter(Pay.id == pay_id).filter(
        Pay.status == PAY_STATUS.SUBMIT).update({
        'price': pay_amount,
        'updated_at': datetime.utcnow()
    })
    if res:
        transaction_table = get_sharding_transaction_table(user_id)
        trans = transaction_table.query.filter(transaction_table.pay_id == pay_id).first()
        if not trans:
            trans = create_sharding_transaction(user_id, {'pay_id': pay_id,
                                                          'type': TRANSACTION_TYPE.IN})
        trans.title = u'充值'
        trans.extend = json.dumps(extend.get('ext'), ensure_ascii=False)
        trans.price = 0 if not pay_amount else Decimal(pay_amount)
        trans.balance = None
        trans.status = TRANSACTION_STATUS.WAIT
        trans.save(auto_commit=False)
        orm.session.commit()
        return True
    else:
        _LOGGER.warn(
            'add pay wait transaction, cocurrency occured!, pay_id[%s]',
            pay_id)
    return False


@sql_wrapper
def add_pay_success_transaction(user_id, pay_id, pay_amount, extend):
    """
    充值成功
    """
    # update pay record status
    res = Pay.query.filter(Pay.id == pay_id).filter(
        Pay.status == PAY_STATUS.SUBMIT).update({
        'status': PAY_STATUS.DONE,
        'price': pay_amount,
        'updated_at': datetime.utcnow()
    })
    pay = Pay.query.filter(Pay.id == pay_id).first()
    bonus_rate = int(get_pay_bonus_rate(pay.pay_type)) * 0.01
    bonus_rate = validate_bonus_rate(pay.pay_type, bonus_rate, pay_amount)
    if res:
        award = calc_first_award(user_id, pay_amount)
        final_amount = 0 if not pay_amount else (Decimal(pay_amount) * Decimal(1 + bonus_rate))
        account = Account.query.filter(Account.id == user_id).with_for_update().first()
        account.balance = float(account.balance) + float(final_amount)
        account.save(auto_commit=False)
        # if award create first_recharge award transaction
        if award:
            create_sharding_transaction(user_id, {
                'user_id': user_id,
                'title': u'首充奖励',
                'type': TRANSACTION_TYPE.RECHARGE_AWARD,
                'price': award,
                'balance': account.balance - float(award),
                'status': TRANSACTION_STATUS.DONE,
                'extend': json.dumps({"amount": pay_amount})
            })
        # if bonus_rate specified for type payment
        if bonus_rate:
            create_sharding_transaction(user_id, {'pay_id': pay_id,
                                                  'type': TRANSACTION_TYPE.PAY_BONUS,
                                                  'title': u'充值回馈',
                                                  'extend': json.dumps(extend.get('ext'), ensure_ascii=False),
                                                  'price': pay_amount * bonus_rate,
                                                  'balance': account.balance - float(pay_amount),
                                                  'status': TRANSACTION_STATUS.DONE,
                                                  })
        # add or modify transaction
        create_sharding_transaction(user_id, {'pay_id': pay_id,
                                              'type': TRANSACTION_TYPE.IN,
                                              'title': u'充值',
                                              'extend': json.dumps(extend.get('ext'), ensure_ascii=False),
                                              'price': pay_amount,
                                              'balance': account.balance,
                                              'status': TRANSACTION_STATUS.DONE,
                                              })
        orm.session.commit()
        tracker.track_recharge_bonus(user_id, pay_amount * bonus_rate)
        return True
    else:
        _LOGGER.warn(
            'add pay success transaction, concurrency occurred!, pay_id[%s]',
            pay_id)
    return False


@sql_wrapper
def add_pay_fail_transaction(user_id, pay_id, pay_amount, extend):
    """
    充值失败
    """
    # update pay record status
    res = Pay.query.filter(Pay.id == pay_id).filter(
        Pay.status == PAY_STATUS.SUBMIT).update({
        'status': PAY_STATUS.FAIL,
        'price': pay_amount,
        'updated_at': datetime.utcnow()
    })
    if res:
        account = Account.query.filter(Account.id == user_id).with_for_update().first()
        create_sharding_transaction(user_id, {'pay_id': pay_id,
                                              'type': TRANSACTION_TYPE.IN,
                                              'title': u'充值失败',
                                              'extend': json.dumps(extend.get('ext'), ensure_ascii=False),
                                              'price': 0 if not pay_amount else Decimal(pay_amount),
                                              'balance': account.balance,
                                              'status': TRANSACTION_STATUS.FAIL,
                                              })
        orm.session.commit()
        return True
    else:
        _LOGGER.warn(
            'add pay fail transaction, cocurrency occured!, pay_id[%s]',
            pay_id)
    return False


@sql_wrapper
def add_system_award_transaction(user_id, award_amount=_FRESH_AWARD_PRICE,
                                 title=u'派奖'):
    account = Account.query.filter(
        Account.id == user_id).with_lockmode('update').one()
    account.balance += Decimal(award_amount)
    create_sharding_transaction(user_id, {'type': TRANSACTION_TYPE.AWARD,
                                          'title': title,
                                          'price': award_amount,
                                          'balance': account.balance,
                                          'status': TRANSACTION_STATUS.DONE,
                                          })
    orm.session.commit()
    _LOGGER.info('add system award, %s %s %s', user_id, award_amount, title)


@sql_wrapper
def get_consume(user_id):
    transaction_table = get_sharding_transaction_table(user_id)
    amount = orm.session.query(orm.func.sum(transaction_table.price)). \
        filter(transaction_table.user_id == user_id). \
        filter(transaction_table.status == TRANSACTION_STATUS.DONE). \
        filter(transaction_table.type == TRANSACTION_TYPE.BALANCE_BUY).first()[0]
    return amount


@sql_wrapper
def get_award(user_id):
    transaction_table = get_sharding_transaction_table(user_id)
    amount = orm.session.query(orm.func.sum(transaction_table.price)). \
        filter(transaction_table.user_id == user_id). \
        filter(transaction_table.status == TRANSACTION_STATUS.DONE). \
        filter(transaction_table.type == TRANSACTION_TYPE.AWARD).first()[0]
    return amount


def create_pay_transaction(user_id, order_id, price, title=u'充值'):
    account = Account.query.with_for_update().filter(
        Account.id == user_id).one()
    if account.balance - price < 0:
        raise err.BalanceInsufficient('balance insufficient')
    account.balance -= price
    account.save(auto_commit=False)
    create_sharding_transaction(user_id, {'type': TRANSACTION_TYPE.BALANCE_BUY,
                                          'title': title,
                                          'price': -price,
                                          'balance': account.balance,
                                          'order_id': order_id,
                                          'status': TRANSACTION_STATUS.DONE,
                                          })


@sql_wrapper
def create_withdraw(user_id, target_type, amount, target_info):
    account = Account.query.with_for_update().filter(Account.id == user_id).one()
    if account.balance < amount:
        raise err.ResourceInsufficient(u'余额不够')
    if account.withdraw < amount:
        raise err.ResourceInsufficient(u'可提现金额不够')
    account.withdraw -= amount
    account.balance -= amount
    account.save(auto_commit=False)

    trans = create_sharding_transaction(user_id, {'type': TRANSACTION_TYPE.WITHDRAW,
                                                  'title': u'提现',
                                                  'price': -amount,
                                                  'balance': account.balance,
                                                  'status': TRANSACTION_STATUS.DONE,
                                                  })
    orm.session.flush()

    withdraw = Withdraw()
    withdraw.trans_id = trans.id
    withdraw.user_id = user_id
    withdraw.status = WITHDRAW_STATUS.WAIT
    withdraw.price = amount
    withdraw.target_type = target_type
    withdraw.after_cash = account.withdraw
    withdraw.extend = json.dumps({'info': target_info}, ensure_ascii=False)
    withdraw.save(auto_commit=False)

    orm.session.commit()
    tracker.track_withdraw(user_id, target_type, calc_withdraw_amount(user_id, amount))
    return withdraw


def parse_query_title(query_dct):
    title_filter_dct = json.loads(query_dct['title'])
    join_filter = []
    if 'multiselect-all' in title_filter_dct['$in']:
        query_dct.pop('title', None)
    else:
        for title in title_filter_dct['$in'][:]:
            t = title.split(',')
            if len(t) == 2:
                filter = {"$and": [
                    {"title": {"$eq": t[0]}},
                    {"type": {"$eq": int(t[1])}}
                    ]}
                join_filter.append(filter)
                title_filter_dct['$in'].remove(title)
            elif title == u'红包抵扣':
                filter = {"$and": [
                    {"extend": {"$like": '%coupon_price%'}},
                    {"type": {"$eq": 4}}
                    ]}
                join_filter.append(filter)
                title_filter_dct['$in'].remove(title)
        if title_filter_dct['$in'] and join_filter:
            title_filter_dct = {"title": title_filter_dct}
            join_filter.append(title_filter_dct)
        if join_filter:
            query_dct['$or'] = json.dumps(join_filter)
            query_dct.pop('title', None)
        else:
            query_dct['title'] = json.dumps(title_filter_dct)
    return query_dct


def map_transaction_title(items):
    for item in items:
        if u'红包' in item.title:
            item.title = u'红包抵扣'
        elif item.title in TITLE_MAPPER:
            if type(TITLE_MAPPER[item.title]) is dict:
                item.title = TITLE_MAPPER[item.title][item.type]
            else:
                item.title = TITLE_MAPPER[item.title]
        # else:
        #    item.title = u'其他'
    return items


def get_user_withdraw(user_id, limit, offset):
    query = Withdraw.query.filter(Withdraw.user_id == user_id).order_by(
        Withdraw.id.desc())
    if limit:
        query = query.limit(limit)
    if offset:
        query = query.offset(offset)
    return query.all()


def list_withdraw(query_dct):
    return list_object(query_dct, Withdraw)


def done_withdraw(ids):
    Withdraw.query.filter(Withdraw.id.in_(ids)).update({
        'status': WITHDRAW_STATUS.DONE
    })
    orm.session.commit()


_ROAD_MAP = {
    WITHDRAW_STATUS.WAIT: [WITHDRAW_STATUS.BACK, WITHDRAW_STATUS.FORBIDDEN],
    WITHDRAW_STATUS.FAIL: [WITHDRAW_STATUS.BACK, WITHDRAW_STATUS.FORBIDDEN],
    WITHDRAW_STATUS.AUTO: [WITHDRAW_STATUS.BACK, WITHDRAW_STATUS.FORBIDDEN],
    WITHDRAW_STATUS.FORBIDDEN: [WITHDRAW_STATUS.BACK],
    WITHDRAW_STATUS.BACK: [],
    WITHDRAW_STATUS.DONE: [],
}

CAN_BE_BACK_STATUS = [WITHDRAW_STATUS.WAIT, WITHDRAW_STATUS.FAIL,
                      WITHDRAW_STATUS.FORBIDDEN, WITHDRAW_STATUS.AUTO]


@sql_wrapper
def check_withdraw(admin_id, withdraw_id, check_status, reason=None):
    update_info = {}
    update_info.update({
        'manual_check_info': {
            'admin_id': admin_id,
            'reason': reason,
            'check_date': local_now().strftime('%Y-%m-%d %H:%M:%S')
        }
    })
    record = Withdraw.query.with_for_update().filter(
        Withdraw.id == withdraw_id).first()
    record.check_status = check_status
    if record and record.status in (WITHDRAW_STATUS.WAIT,):
        updated_info = json.loads(record.extend)
        updated_info.update(update_info)
        if check_status == CHECK_STATUS.SUCC:
            record.status = WITHDRAW_STATUS.AUTO
        record.extend = json.dumps(updated_info, ensure_ascii=False)
    record.save()


@sql_wrapper
def batch_check_withdraws(admin_id, ids, check_status):
    if not ids:
        return
    update_info = {}
    update_info.update({
        'manual_check_info': {
            'admin_id': admin_id,
            'check_date': local_now().strftime('%Y-%m-%d %H:%M:%S')
        }
    })
    for id in ids:
        record = Withdraw.query.with_for_update().filter(
            Withdraw.id == id).first()
        record.check_status = check_status
        if record and record.status in (WITHDRAW_STATUS.WAIT,):
            updated_info = json.loads(record.extend)
            updated_info.update(update_info)
            if check_status == CHECK_STATUS.SUCC:
                record.status = WITHDRAW_STATUS.AUTO
            record.extend = json.dumps(updated_info, ensure_ascii=False)
        record.save()


def update_withdraw_status(admin_id, id, info):
    new_status = info['status']
    old_status = get_withdraw(id).status
    if _ROAD_MAP[old_status] == 'all' or new_status in _ROAD_MAP[old_status]:
        if new_status == WITHDRAW_STATUS.BACK:
            withdraw_back(admin_id, id, info['reason'])
            async_job.stats_withdraw.delay([id])
            return
        else:
            item = Withdraw.query.filter(Withdraw.id == id).with_lockmode('update').first()
            extend = json.loads(item.extend)
            if new_status == WITHDRAW_STATUS.AUTO:
                extend.update({
                    'manual_auto_trans_info': {
                        'admin_id': admin_id,
                        'pay_date': local_now().strftime('%Y-%m-%d %H:%M:%S'),
                    }
                })
            if new_status == WITHDRAW_STATUS.DONE:
                extend.update({
                    'manual_done_trans_info': {
                        'admin_id': admin_id,
                        'pay_date': local_now().strftime('%Y-%m-%d %H:%M:%S'),
                    }
                })
            if new_status == WITHDRAW_STATUS.FORBIDDEN:
                if item.check_status == CHECK_STATUS.WAITING:
                    item.check_status = CHECK_STATUS.FAIL
                extend.update({
                    'manual_forbidden_trans_info': {
                        'admin_id': admin_id,
                        'pay_date': local_now().strftime('%Y-%m-%d %H:%M:%S'),
                    }
                })

            item.status = new_status
            item.extend = json.dumps(extend, ensure_ascii=False)
            item.save()
            if new_status == WITHDRAW_STATUS.FORBIDDEN:
                tracker.track_withdraw(item.user_id, item.target_type, calc_withdraw_amount(item.user_id, item.price),
                                       status=WITHDRAW_STATUS.FORBIDDEN)
                tracker.track_withdraw(item.user_id, item.target_type, -calc_withdraw_amount(item.user_id, item.price))
    else:
        raise err.ParamError(u'无效操作，请检查订单状态!')


def get_last_info(user_id, target_type=None):
    query = Withdraw.query.filter(
        Withdraw.user_id == user_id).order_by(Withdraw.id.desc())
    if target_type is not None:
        query = query.filter(Withdraw.target_type == target_type)
    return query.first()


@sql_wrapper
def batch_update_withdraws(admin_id, ids, status):
    if not ids:
        return
    update_info = {}
    if status == WITHDRAW_STATUS.DONE:
        update_info.update({
            'manual_trans_info': {
                'admin_id': admin_id,
                'pay_date': local_now().strftime('%Y-%m-%d %H:%M:%S'),
            }
        })
    for id in ids:
        record = Withdraw.query.with_for_update().filter(
            Withdraw.id == id).first()
        if record:
            updated_info = json.loads(record.extend)
            updated_info.update(update_info)
            record.status = status
            record.extend = json.dumps(updated_info, ensure_ascii=False)
            record.save()


@sql_wrapper
def get_withdraw(withdraw_id):
    return Withdraw.query.filter(Withdraw.id == withdraw_id).first()


@sql_wrapper
def list_withdraw_records(query_dct):
    risk_type = 0
    if 'risk_type' in query_dct:
        risk_type = long(query_dct['risk_type'])

    query = Withdraw.query.filter(generate_filter(
        parse_query_dct(query_dct, Withdraw), Withdraw))
    # auto_risk为true时，查询提现中含有风控信息的订单
    if query_dct.get('$auto_risk'):
        query = query.filter(Withdraw.check_status >= CHECK_STATUS.WAITING)

    if RISK_STATUS.get_label(risk_type):
        query = query.filter(Withdraw.extend.like(u'%%{}%%'.format(RISK_STATUS.get_label(risk_type))))

    user_id = query_dct.pop('user_id', None)
    if user_id:
        query = query.filter(Withdraw.user_id == user_id)
    total_count = get_count(query)
    orderby = get_orderby(query_dct.get('$orderby'), Withdraw)
    if orderby is not None:
        query = query.order_by(orderby)
    query = paginate(query, query_dct)
    return query.all(), total_count


@sql_wrapper
def get_withdraw_records(query_dct):
    updated_at = json.loads(query_dct.get('updated_at'))
    begin = updated_at.get('$gte')
    end = updated_at.get('$lte')
    status = query_dct.get('status')
    query = Withdraw.query.filter(Withdraw.updated_at > begin).filter(Withdraw.updated_at < end).filter(
        Withdraw.status == int(status))
    return query.all()


@sql_wrapper
def get_withdraw_count_amount(user_id, target_type=CHECK_STATUS.WAITING):
    query = Withdraw.query.filter(
        Withdraw.user_id == user_id, Withdraw.target_type == target_type)
    total_amount = 0
    if query:
        total_amount = query.with_entities(func.sum(Withdraw.price)).scalar() or 0
        total_count = get_count(query)
    return total_amount, total_count


@sql_wrapper
def export_withdraw_records(query_dct):
    items = get_withdraw_records(query_dct)
    resp_items = []
    for item in items:
        withdraw_at = utc_to_local_str(item.created_at)
        updated_at = utc_to_local_str(item.updated_at)
        withdraw_info = json.loads(item.extend or '{}')
        withdraw_status = WITHDRAW_STATUS.get_label(item.status)
        name = withdraw_info.get('name')
        phone = withdraw_info.get('phone')
        third_id = '-'
        pay_date = '-'
        withdraw_chn = '-'
        if (item.target_type == WITHDRAW_TYPE.ALIPAY and
                item.status == WITHDRAW_STATUS.DONE and
                'auto_trans_info' in withdraw_info):
            third_id = withdraw_info['auto_trans_info'].get('order_id') or '-'
            pay_date = withdraw_info['auto_trans_info'].get('pay_date')
            payer_no = withdraw_info['auto_trans_info'].get('payer_no')
            if payer_no == 'group_new_withdraw':
                withdraw_chn = u'万豪下分'
            else:
                withdraw_chn = u'官方下分'
        real_amount = item.real_price or calc_withdraw_amount(item.user_id, item.price)
        data_row = [item.id, item.user_id, item.price, real_amount,
                    WITHDRAW_TYPE.get_label(item.target_type), withdraw_chn,
                    withdraw_at, withdraw_status, updated_at, name, phone]
        if item.target_type == WITHDRAW_TYPE.ALIPAY:
            data_row += [withdraw_info.get('alipay_num'), '-']
        elif item.target_type == WITHDRAW_TYPE.BANKCARD:
            data_row += [withdraw_info.get('bank_no'), withdraw_info.get('bank_addr')]
        elif item.target_type == WITHDRAW_TYPE.QQPAY:
            data_row += [withdraw_info.get('qq_no'), '-']
        elif item.target_type == WITHDRAW_TYPE.WECHATPAY:
            data_row += [withdraw_info.get('wechat_no'), '-']
        else:
            data_row += ['-', '-']
        data_row += [third_id, pay_date]
        resp_items.append(data_row)
    return resp_items


@sql_wrapper
def get_cash_records(user_id, limit=0, offset=0):
    query = Withdraw.query.filter(Withdraw.user_id == user_id)
    query = query.order_by(Withdraw.created_at.desc())
    if limit > 0:
        query = query.limit(limit)
    if offset > 0:
        query = query.offset(offset)
    return query.all()


@sql_wrapper
def update_withdraw(admin_id, record_id, status):
    item = Withdraw.query.filter(Withdraw.id == record_id).with_lockmode('update').first()
    item.status = status
    updated_info = json.loads(item.extend or '{}')
    if status == WITHDRAW_STATUS.DONE:
        updated_info.update({
            'manual_trans_info': {
                'admin_id': admin_id,
                'pay_date': local_now().strftime('%Y-%m-%d %H:%M:%S'),
            }
        })
    item.withdraw_info = json.dumps(updated_info, ensure_ascii=False)
    item.save()


@sql_wrapper
def withdraw_back(admin_id, withdraw_id, reason):
    withdraw = Withdraw.query.with_for_update().filter(
        Withdraw.status.in_(CAN_BE_BACK_STATUS)).filter(
        Withdraw.id == withdraw_id).one()
    before_status = withdraw.status
    withdraw.status = WITHDRAW_STATUS.BACK

    account = Account.query.with_for_update().filter(
        Account.id == withdraw.user_id).one()
    account.balance += withdraw.price
    account.withdraw += withdraw.price
    account.save(auto_commit=False)

    create_sharding_transaction(withdraw.user_id, {'type': TRANSACTION_TYPE.WITHDRAW_FAIL_REFUND,
                                                   'title': u'提现失败返款',
                                                   'price': withdraw.price,
                                                   'balance': account.balance,
                                                   'status': TRANSACTION_STATUS.DONE,
                                                   })

    extend = json.loads(withdraw.extend)

    if before_status != WITHDRAW_STATUS.FORBIDDEN:
        before_status = WITHDRAW_STATUS.WAIT
    if withdraw.check_status == CHECK_STATUS.WAITING:
        withdraw.check_status = CHECK_STATUS.FAIL
        if 'manual_check_info' not in extend:
            extend['manual_check_info'] = {
                'admin_id': admin_id,
                'reason': u'返款状态自动更新',
                'check_date': local_now().strftime('%Y-%m-%d %H:%M:%S')
            }
    extend['back_reason'] = reason
    extend['back_trans_info'] = {
        'admin_id': admin_id,
        'back_date': local_now().strftime('%Y-%m-%d %H:%M:%S'),
    }
    withdraw.extend = json.dumps(extend, ensure_ascii=False)
    withdraw.save(auto_commit=False)
    orm.session.commit()
    tracker.track_withdraw(account.id, withdraw.target_type,
                           -calc_withdraw_amount(account.id, withdraw.price), before_status)


def validate_bonus_rate(pay_type, bonus_rate, pay_amount):
    if bonus_rate > MAX_BONUS_RATE or bonus_rate < 0:
        _LOGGER.info('Invalid bonus rate, bonus rate is 0 now')
        return 0
    if pay_amount < MIN_BONUS_RATE_PAY_AMOUNT:
        _LOGGER.info('Pay amount too low, bonus rate is 0 now')
        return 0
    return bonus_rate


def create_sharding_transaction(user_id, attributes):
    transaction_table = get_sharding_transaction_table(user_id)
    instance = transaction_table()
    instance.user_id = user_id
    for k, v in attributes.iteritems():
        if hasattr(transaction_table, k) and k not in ('updated_at', 'created_at'):
            setattr(instance, k, v)
        else:
            raise DbError('illegal transaction attribute: %s' % k)
    instance.save(auto_commit=False)
    return instance


def get_sharding_transaction_table(user_id):
    table = TRANSACTION_SHARDING_CONFIG.get(int(user_id) % TRANSACTION_SHARDING_COUNT)
    if not table:
        raise DbError('transaction table not found. user_id: %s' % user_id)
    return table


def get_last_success_pay(user_id):
    return Pay.query.filter(Pay.user_id == user_id). \
        filter(Pay.status == PAY_STATUS.DONE). \
        order_by(Pay.updated_at.desc()).first()


def _decrease_from_out_to_in(amount_list, decr):
    # 从amount_list里每一项扣钱，规则是优先扣取前面的，当前不够时，再扣取后面项的
    first = amount_list[0]
    result = [first - decr]
    for each in amount_list[1:]:
        diff = first - each
        result.append(each if decr <= diff else (first - decr))
    return result


def _increase_balance(user_id, amount, balance_type=BALANCE_TYPE.BALANCE, balance_dct={}):
    """
    增加用户账号余额
    :param user_id:
    :param amount: 增加的钱，单位是毫
    :param balance_type: BALANCE_TYPE
    :return:
    """
    assert user_id > 0
    assert isinstance(amount, int)
    _LOGGER.info('user_id increase account amount: %s %s %s %s' %
                 (user_id, amount, balance_type, balance_dct))
    if balance_type == BALANCE_TYPE.SPECIFIED:
        assert min(value for value in balance_dct) >= 0
    elif amount <= 0:
        _LOGGER.info('user_id increase less zero amount transaction: %s %s' % (user_id, amount))
        return
    account = Account.query.with_for_update().filter(Account.id == user_id).first()
    orm.session.expire(account)
    balance = convert_yuan_to_hao(account.balance)
    withdraw = convert_yuan_to_hao(account.withdraw)
    if not balance_type == BALANCE_TYPE.SPECIFIED:
        balance += amount
    if balance_type == BALANCE_TYPE.AGENT:
        withdraw += amount
    elif balance_type == BALANCE_TYPE.WITHDRAW:
        withdraw += amount
    elif balance_type == BALANCE_TYPE.SPECIFIED:
        balance += balance_dct.get('balance', 0)
        withdraw += balance_dct.get('withdraw', 0)
    account.balance = convert_hao_to_yuan(balance)
    account.withdraw = convert_hao_to_yuan(withdraw)
    account.save(auto_commit=False)
    orm.session.flush()
    _LOGGER.info('the current account: %s %s' % (account.balance, account.withdraw))
    return account


def _decrease_balance(user_id, amount, balance_type=BALANCE_TYPE.BALANCE):
    """
    减少用户账号余额
    :param user_id:
    :param amount: 扣除的钱，单位是分
    :param balance_type: BALANCE_TYPE
    :return: (decr_balance, decr_withdraw) 返回2个余额分别实际扣减的金额， 单位是分
    """
    assert user_id > 0
    assert isinstance(amount, int)
    assert balance_type in [BALANCE_TYPE.BALANCE, BALANCE_TYPE.WITHDRAW, BALANCE_TYPE.AGENT]
    _LOGGER.info('user_id decrease account amount: %s %s %s' % (user_id, amount, balance_type))
    if amount <= 0:
        _LOGGER.info('user_id decrease less zero amount transaction: %s %s' % (user_id, amount))
        return
    account = Account.query.with_for_update().filter(Account.id == user_id).first()
    orm.session.expire(account)
    balance = convert_yuan_to_hao(account.balance)
    withdraw = convert_yuan_to_hao(account.withdraw)

    assert balance >= withdraw, 'user account balance data error: %s %s %s' % (user_id, balance, withdraw)

    if balance < amount:
        raise err.ResourceInsufficient(u'当前余额不足')
    if check_banker_lock(user_id):
        raise err.LockError('bull banker lock')
    if balance - amount < BULL_LOCK_RATIO * convert_yuan_to_hao(get_lock_amount(user_id)):
        raise err.BalanceInsufficient(u'账户余额不足')
    elif balance_type == BALANCE_TYPE.WITHDRAW:
        if withdraw < amount:
            raise err.ParamError(u'{}可提现余额不足:仅有{}'.format('user_id', account.withdraw))
        balance -= amount
        withdraw -= amount
    elif balance_type == BALANCE_TYPE.BALANCE:
        balance, withdraw = _decrease_from_out_to_in([balance, withdraw], amount)
    account.balance = convert_hao_to_yuan(balance)
    account.withdraw = convert_hao_to_yuan(withdraw)
    account.save(auto_commit=False)
    orm.session.flush()
    _LOGGER.info('the current account: %s %s' % (account.balance, account.withdraw))
    return account


def get_out_trans_id(out_trans_id):
    query = Transaction.query.filter(Transaction.out_trans_id == out_trans_id).first()
    if query:
        return False
    return True


def create_transaction(transaction_data, disable_agent_trans=False):
    data = copy.deepcopy(transaction_data)
    check_params(data, ['user_id', 'type', 'title', 'price', 'balance_type'])
    assert data['balance_type'] in BALANCE_TYPE.to_dict()
    if data['balance_type'] == BALANCE_TYPE.SPECIFIED:
        check_params(data, ['balance_dct'])
        for key in data.get('balance_dct', {}):
            assert data['balance_dct'][key] >= 0
    else:
        assert data['price'] != 0

    if data.get('out_trans_id'):
        query = get_out_trans_id(data.get('out_trans_id'))
        if not query:
            raise err.ParamError(u'交易重复')

    # start transaction
    amount = data['price']
    if amount > 0 or data['balance_type'] == BALANCE_TYPE.SPECIFIED:
        account = _increase_balance(data['user_id'], amount, data['balance_type'], data.get('balance_dct', {}))
    elif amount < 0:
        account = _decrease_balance(data['user_id'], -amount, data['balance_type'])
    data['price'], data['balance'] = convert_hao_to_yuan(amount), account.balance
    # insert transaction
    data.pop('balance_type')
    trans = create_sharding_transaction(data['user_id'], data)
    data['id'] = trans.id
    return trans.id